# %%
# 确认数据文件
import numpy as np
import pandas as pd

import reader
from common import *

(year, month), _ = year_month()

FP_CHAIJI = f"report/1-拆机/{year}{month:02}_拆机工单地市稽核明细表.xlsx"
FP_KUANDAI = (
    f"report/2-宽带装移机/{year}{month:02}_宽带装移机工单地市稽核明细表.xlsx"
)
FP_TV = f"report/3-电视装移机/{year}{month:02}_电视装移机地市稽核明细表.xlsx"
FP_IMS = (
    f"report/4-IMS装移机/{year}{month:02}_IMS装移机工单地市稽核明细表.xlsx"
)
FP_ZUWANG = (
    f"report/5-智能组网/{year}{month:02}_智能组网工单地市稽核明细.xlsx"
)
FP_TISU = f"report/6-提速/{year}{month:02}_提速工单稽核明细表.xlsx"
FP_ANFANG = (
    f"report/7-智能安防/{year}{month:02}_安防工单地市稽核结果.xlsx"
)
FP_AJTV = (
    f"report/8-爱家TV/{year}{month:02}_爱家TV开通地市稽核明细.xlsx"
)

check_files(
    FP_CHAIJI,
    FP_KUANDAI,
    FP_TV,
    FP_IMS,
    FP_ZUWANG,
    FP_TISU,
    FP_ANFANG,
    FP_AJTV,
)

# %%
# 读取数据
df_chaiji = pd.read_excel(FP_CHAIJI)
df_kuandai = pd.read_excel(FP_KUANDAI)
df_tv = pd.read_excel(FP_TV)
df_ims = pd.read_excel(FP_IMS)
df_zuwang = pd.read_excel(FP_ZUWANG)
df_tisu = pd.read_excel(FP_TISU)
df_anfang = pd.read_excel(FP_ANFANG)
df_ajtv = pd.read_excel(FP_AJTV)
df_tv.电视账号 = df_tv.电视账号.astype("string")
df_ajtv.rename(
    columns={
        "订单行号": "工单号",
        "CRM订单号": "CRM业务流水号",
    },
    inplace=True,
)
df_ajtv.电视账号 = df_ajtv.电视账号.astype("string")

# %%
# 筛选【地市稽核结果】通过的工单
df_list = [
    df_chaiji,
    df_kuandai,
    df_tv,
    df_ims,
    df_zuwang,
    df_tisu,
    df_anfang,
    # df_ajtv,
]
for df in df_list:
    df.drop(df[df.地市稽核结果 != S_PASSED].index, inplace=True)

df_anfang_ptb = df_anfang[df_anfang.业务结算类型 == "普通版"]
df_anfang_paxc = df_anfang[df_anfang.业务结算类型 == "平安乡村"]
df_zuwang_sbaz = df_zuwang[df_zuwang.服务类型 == "设备安装"]
df_zuwang_cfw = df_zuwang[df_zuwang.服务类型 == "纯服务"]


# %%
# 生成首列【CRM业务流水号】
df_orders = pd.concat(
    [df for df in df_list], ignore_index=True
)
df_crm = df_orders.copy()
df_crm.drop_duplicates(subset=["CRM业务流水号"], inplace=True)
df_crm = df_crm["CRM业务流水号"]

# %%
# 定义业务优先次序
process_order = [
    ["宽带开通", df_kuandai],
    ["电视开通", df_tv],
    ["IMS开通", df_ims],
    # ["爱家TV", df_ajtv],
    ["智能组网（设备安装）", df_zuwang_sbaz],
    ["智能组网（纯服务）", df_zuwang_cfw],
    ["安防（平安乡村）", df_anfang_paxc],
    ["安防（基础版）", df_anfang_ptb],
    ["提速", df_tisu],
    ["拆机", df_chaiji],
]

COLUMNS = [
    "月份",
    "地市",
    "工单号",
    "CRM业务流水号",
    "派单人",
    "派单时间",
    "归档时间",
    "时长",
    "操作类型",
    "装维人员/用户班人员",
    "五级地址ID",
    "客户类型",
    "接入方式",
    "宽带帐号",
    "电视账号",
    "多媒体家庭电话",
]
df_main = pd.DataFrame(columns=COLUMNS)
df_main["CRM业务流水号"] = df_crm
COLUMNS.remove("CRM业务流水号")

# %%
# 倒序扫描各类业务是否同装
for name, df in process_order[::-1]:
    df_main = df_main.merge(
        df[["CRM业务流水号", "工单号"]],
        on="CRM业务流水号",
        how="left",
        suffixes=["", "_rhs"],
    )
    df_main[f"是否{name}"] = S_NO
    mask = df_main.工单号_rhs.notna()
    df_main.loc[mask, f"是否{name}"] = S_YES
    df_main.loc[mask, "工单号"] = df_main.工单号_rhs
    df_main.drop("工单号_rhs", axis=1, inplace=True)

# %%
# 读取当月归档工单
FP_GONGDAN_DANGYUE = (
    f"data/0-工单/家客工单导出_潮州_{year}-{month:02}.zip"
)
df_dy = reader.read_gongdan(FP_GONGDAN_DANGYUE)
df_dy["时长"] = (
    df_dy["归档时间"] - df_dy["派单时间"]
) / np.timedelta64(1, "h")
df_dy["月份"] = f"{month}月"
df_dy["地市"] = "潮州"
df_info = pd.merge(
    df_main["工单号"], df_dy, on="工单号", how="left"
)
df_main[COLUMNS] = df_info[COLUMNS]

# %%
# 【电视账号】、【多媒体家庭电话】
df_main = df_main.merge(
    df_tv[["CRM业务流水号", "电视账号"]],
    on="CRM业务流水号",
    how="left",
    suffixes=["", "_rhs"],
)
mask = df_main.电视账号_rhs.notna()
df_main.loc[mask, "电视账号"] = df_main.电视账号_rhs
df_main.drop("电视账号_rhs", axis=1, inplace=True)

df_main = df_main.merge(
    df_ims[["CRM业务流水号", "多媒体家庭电话"]],
    on="CRM业务流水号",
    how="left",
    suffixes=["", "_rhs"],
)
mask = df_main.多媒体家庭电话_rhs.notna()
df_main.loc[mask, "多媒体家庭电话"] = df_main.多媒体家庭电话_rhs
df_main.drop("多媒体家庭电话_rhs", axis=1, inplace=True)

# %%
# 同开业务
df_main["同开业务"] = None


def multi_order(row):
    orders = []
    for p, _ in process_order:
        if row[f"是否{p}"] == S_YES:
            orders.append(p)
    if len(orders) > 1:
        row["同开业务"] = ",".join(orders)

    return row


df_main = df_main.apply(multi_order, axis=1)


# %%
# 同装汇总表
df_main.to_excel(
    f"report/0-汇总/{year}{month:02}_同装汇总表.xlsx",
    index=False,
)
